<?php defined('SYSPATH') or die('No direct access allowed.');

class Model_Contact extends ORM {

    protected $_belongs_to = array(
                'sender'    => array('model' => 'user', 'foreign_key' => 'sender_id'),
                'recipient' => array('model' => 'user', 'foreign_key' => 'recipient_id')
    );
    
    public function get_contacts($user_id)
    {
        $temp_contacts = $this->select('user_profiles.name', 
                                      'user_profiles.surname', 
                                      'user_profiles.description', 
                                      'user_profiles.url',
                                      array('user_profiles.file_id','photo'),
                                      array('files.id','file_id'),
                                      array('files.name','file_name'),
                                      array('files.extension','file_extension'),
                                      'user_privacy.new_project_notification',
                                      'users.email')
                                     // array(DB::expr('CASE `sender_id` WHEN '.$user_id.' THEN `recipient_id` else `sender_id` END'),'contact_id'))
                             ->join('user_profiles')->on(DB::expr('CASE WHEN `sender_id` = '.$user_id.' THEN `recipient_id` else `sender_id` END'), '=', 'user_profiles.user_id')
                             ->join('user_privacy')->on('user_privacy.user_id', '=', 'user_profiles.user_id')
                             ->join('users')->on('user_privacy.user_id', '=', 'users.id')
                             ->join('files', 'LEFT')->on('user_profiles.file_id', '=', 'files.id')
                             ->where('status', '=', 1)
                             ->and_where_open()
                                ->where('sender_id','=', $user_id)
                                ->or_where('recipient_id','=', $user_id)
                             ->and_where_close()
                             ->find_all();
        
        $contacts = array();
        foreach($temp_contacts as $contact)
        { 
            $contact->photo = Media::get_file_path($contact->file_name, 
                                            $contact->file_id, 
                                            $contact->file_extension, 
                                            true, 
                                            'mini_square', 
                                            false);
            $contacts[] = $contact;
        }
        return $contacts;
    }
    
    public function in_contact($user_one_id, $user_two_id)
    {
        if ($user_one_id == false or $user_two_id == false) return false;
        elseif ($user_one_id == $user_two_id) return false;
        
       $in_contact = DB::select(array('COUNT("*")', 'total_rows'))
                       ->from('contacts')
                         ->where_open()
                            ->where('recipient_id','=',$user_one_id)
                            ->where('sender_id','=',$user_two_id)
                         ->where_close()
                         ->or_where_open()
                            ->where('recipient_id','=',$user_two_id)
                            ->where('sender_id','=',$user_one_id)
                         ->or_where_close()
                         ->as_object()
                         ->execute();
        
        return (bool) $in_contact[0]->total_rows;

    }
    
    public function get_contacts_proposals($user_id, $amount = null, $mutual_amount = 1, $rand = false)
    {
        $query = 'SELECT fof.amount_mutual, 
                         PROFILE.name, 
                         PROFILE.surname, 
                         PROFILE.description,
                         PROFILE.url,
                         PROFILE.file_id,
                         FILE.id AS file_id,
                         FILE.name AS file_name,
                         FILE.extension AS file_extension 
                  FROM
                  (
                      SELECT CASE sender_id WHEN FRIENDS.id THEN recipient_id else sender_id END AS proposing_friend,
                      COUNT(*) AS amount_mutual


                     FROM (SELECT CASE sender_id WHEN :user_id THEN recipient_id else sender_id END AS id 
                           FROM contacts
                           WHERE (sender_id = :user_id OR recipient_id = :user_id ) and status = 1) AS FRIENDS


                     JOIN contacts AS CS 
                     ON (sender_id = friends.id OR recipient_id = friends.id and status = 1) 
                     AND sender_id <> :user_id AND recipient_id <> :user_id 

                     WHERE (sender_id NOT IN (SELECT CASE sender_id WHEN :user_id THEN recipient_id else sender_id END AS id 
                                              FROM contacts
                                              WHERE (sender_id = :user_id OR recipient_id = :user_id ))

                     OR recipient_id NOT IN (SELECT CASE sender_id WHEN :user_id THEN recipient_id else sender_id END AS id 
                                              FROM contacts
                                              WHERE (sender_id = :user_id OR recipient_id = :user_id )))

                     GROUP BY CASE sender_id WHEN FRIENDS.id THEN recipient_id else sender_id END
                     HAVING (COUNT(*)) > :mutual_amount 
                ) as fof

                JOIN user_profiles AS PROFILE ON (PROFILE.user_id = fof.proposing_friend) 
                LEFT JOIN files AS FILE ON (FILE.id = PROFILE.file_id) ';
        
        if ($rand)
        {
           $query = $query.' ORDER BY RAND() ';
        }
        
        if ($amount !== null)
        {
            $query = $query.' LIMIT '.$amount; 
            
        }
        
        
        $contacts_of_contacts = DB::query(DATABASE::SELECT,$query)
                                    ->bind(':user_id', $user_id)
                                    ->bind(':mutual_amount', $mutual_amount)
                                    ->execute();
        
        //$user_profile_model = ORM::factory('User_Profile');
        //$user_profile_model->where('user_id', 'IN', $contacts_of_contacts);
        

        
        //$user_profiles = $user_profile_model->find_all();
        
        return $contacts_of_contacts;
    }
    
    public function get_mutual_friends($one_user_id, $second_user_id)
    {
             if ($one_user_id == false or $second_user_id == false) return false;
             elseif ($one_user_id == $second_user_id) return false;
        
            $other_friends = DB::select(array(DB::expr('CASE `sender_id` WHEN '.$one_user_id.' THEN `recipient_id` else `sender_id` END'),'id'))
                                                               ->from('contacts')
                                                               ->where('status', '=', 1)
                                                               ->and_where_open()
                                                               ->where('sender_id','=', $one_user_id)
                                                               ->or_where('recipient_id','=', $one_user_id)
                                                               ->and_where_close();
        
            $my_friends = DB::select(array(DB::expr('CASE `sender_id` WHEN '.$second_user_id.' THEN `recipient_id` else `sender_id` END'),'id'))
                                                                   ->from('contacts')                                                          
                                                                   ->where('status', '=', 1)
                                                                   ->and_where_open()
                                                                   ->where('sender_id','=', $second_user_id)
                                                                   ->or_where('recipient_id','=', $second_user_id)
                                                                   ->and_where_close();


            $sub_level_1 = DB::select('my_friends.id')->from(array($my_friends,'my_friends'))
                                       ->join(array($other_friends,'other_friends'))
                                       ->on('other_friends.id', '=', 'my_friends.id');

            $user_profile_model = ORM::factory('User_Profile');
            $mutual_friends = $user_profile_model->where('user_id','IN',$sub_level_1)
                                                 ->find_all();
            
            return $mutual_friends ;
    }
    
}